USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[ScoreByQuestion]    Script Date: 7/8/2013 7:22:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[ScoreByQuestion] @CRN int, @year int, @semester int

AS
BEGIN


--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013

SELECT 
	*
FROM 

	(SELECT 
		A.Question,
		A.score,
		AVG(score) over (partition by CHECKSUM( A.Question, A.subjectCode)) 'DepartmentAverage',
		AVG(score) over (partition by CHECKSUM( A.Question, A.instructorID)) 'InstructorAverage',
		AVG(score) over (partition by CHECKSUM( A.Question, A.courseNumber)) 'CourseAverage',
		A.instructorID,
		A.Instructor,
		A.bannerCRN,
		A.semester,
		A.year,
		A.block,
		A.courseNumber,
		A.subjectCode


	FROM 
	(
		SELECT 
			CONVERT(nvarchar(400),Q.text) Question,
			AVG(CASE 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
			END) AS score,
			ST.instructorID,
			(UI.FirstName + ' ' + UI.LastName) AS Instructor,
			CS.bannerCRN,
			CS.semester,
			CS.year,
			CS.block,
			CS.courseNumber,
			CS.subjectCode

		FROM SectionsTaught ST
			INNER JOIN Results R ON R.sectionID = ST.sectionID
				AND (R.deleted = 0 OR R.deleted IS NULL)
				--AND R.startTime BETWEEN DATEADD(year, -5, GETDATE()) AND GETDATE() --Last 5 years
				--AND R.startTime BETWEEN @startDate AND @endDate
				--AND ST.instructorID = 887969243
			INNER JOIN CourseSections CS ON CS.ID = R.sectionID
				AND CS.subjectCode = 'CS'
				AND CS.year = @year 
				AND CS.semester = @semester
			INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			INNER JOIN Questions Q ON Q.ID = QR.questionID
			INNER JOIN Tests T ON T.ID = Q.testID 
				AND T.typeID = 5 --Course Eval
			INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
			INNER JOIN Distractors D ON D.ID = DR.distractorID
			--INNER JOIN Users U ON U.Id = R.studentID
			--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
			INNER JOIN Users UI ON UI.Id = ST.instructorID

		GROUP BY CONVERT(nvarchar(400),Q.text), 
			ST.instructorID, 
			(UI.FirstName + ' ' + UI.LastName),
			CS.bannerCRN,
			CS.semester,
			CS.year,
			CS.block,
			CS.courseNumber,
			CS.subjectCode) A ) B
WHERE
	bannerCRN = @CRN
	

END
GO

